
* Input:
* OrigData/CERData_May16.xlsx

* Output:
* Data/postcode_month_PVinstall.dta
* Data/postcode_year_PVinstall.dta


 *****************************
 **** IMPORT INSTALL DATA ****
 *****************************

set more off 

clear
import excel using OrigData/CERData_May16.xlsx, allstring firstrow




ds A, not 
local newlist = r(varlist)

 foreach v of varlist `newlist' {
   local x : variable label `v'
   local q_`v'  =strtoname("`x'")
   ren `v' `q_`v''
 }
 
 
 rename A postcode
 
 destring _all, replace
 
 
foreach var in Apr_ May_ Jun_ Jul_ Aug_ Sep_ Oct_ Nov_ Dec_ Jan_ Feb_ Mar_ {
	
	forval x = 1(1)9 {
	capture rename `var'0`x' `var'`x'
		}
	
	}
	

 
  reshape long Apr_ May_ Jun_ Jul_ Aug_ Sep_ Oct_ Nov_ Dec_ Jan_ Feb_ Mar_ , i(postcode) j(year)
  
  order postcode year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
  
  
   
foreach var in Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec {
	
	capture rename `var'_  month`var'
	
	}
  
  reshape long "month" , i(postcode year) j(cal) string
  
  rename month newinstallations
    
  rename year year_orig
  gen year = 2000 + year
  
  gen month_year_str =  cal + string(year)
  gen month_year = mofd(date(month_year_str, "MY"))
  format month_year %tm
  drop month_year_str year_orig cal
  
 
  
  by postcode (month_year), sort: gen cuminstallations = sum(newinstallations)
  
  order postcode month_year year new cum 
  
  
  * keep Victoria only
  
  keep if postcode >= 3000 & postcode <=3999
  
  drop if newinstall ==. // drops 2016 - no data yet
  
  gen contract_start_month = month_year

  
  save Data/postcode_month_PVinstall.dta, replace
  
  
  
  
  
  
    collapse (sum) newinstallations (lastnm) cuminstallations, by(postcode year)

	
	save Data/postcode_year_PVinstall.dta, replace

	
	
	
	** quarterly
	
	use Data/postcode_month_PVinstall.dta, clear
gen date = dofm(month_year)	
gen month = month(date)
	
gen quarter =1 if month < = 3
replace quarter =2 if month > 3 & month < =6
replace quarter = 3 if month > 6 & month <=9
replace quarter = 4 if month >9 & month <=12

gen contract_quarter = yq(year,quarter)
format contract_quarter %tq


collapse (sum) newinstallations (lastnm) cuminstallations, by(postcode contract_quarter)


save Data/postcode_quarterinstall.dta, replace









